﻿Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Imports DevExpress.XtraGrid
Imports DevExpress.XtraGrid.Views.Grid
Imports DevExpress.XtraGrid.Views.Grid.ViewInfo
Imports DevExpress.XtraEditors



Public Class Technicien
    Private dataAdapter As New SqlClient.SqlDataAdapter()
    Private dataAdapterM3 As New OleDbDataAdapter
    Private bindingSource1 As New BindingSource()
    Private bindingSource2 As New BindingSource()

    Private Sub XtraForm1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing

        My.Settings.XtraForm1Size = Me.Size
        My.Settings.XtraForm1Pos = Me.Location
        GridView1.SaveLayoutToXml(My.Settings.ConfigPath & "\TECHGW01.xml")
        GridView2.SaveLayoutToXml(My.Settings.ConfigPath & "\TECHGW02.xml")

    End Sub

    Private Sub XtraForm1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'DevExpress.Skins.SkinManager.EnableFormSkins()
        'DevExpress.UserSkins.OfficeSkins.Register()

        Me.Location = My.Settings.XtraForm1Pos
        Me.Size = My.Settings.XtraForm1Size
        Me.SplitContainerControl1.SplitterPosition = My.Settings.Split1

        ' Bind the DataGridView to the BindingSource
        ' and load the data from the database.


        Me.GridControl1.DataSource = Me.bindingSource1
        GetData("SELECT * FROM F0010")

        Me.GridControl2.DataSource = Me.bindingSource2
        GetDataM3("SELECT EAEMNM, EADIVI,EAEMNO,EAREAR,EAWHLO,EACAPH,EACAPH,EAPNCO,EAPHNO,EADEPT, EASRES FROM CEAEMP WHERE EADIVI = '" & Trim(My.Settings.DIVI) & "'")

        If File.Exists(My.Settings.ConfigPath & "\TECHGW01.xml") Then GridView1.RestoreLayoutFromXml(My.Settings.ConfigPath & "\TECHGW01.xml")
        If File.Exists(My.Settings.ConfigPath & "\TECHGW02.xml") Then GridView2.RestoreLayoutFromXml(My.Settings.ConfigPath & "\TECHGW02.xml")

        My.Settings.TechUpdatePath = GetSetupValue("TechUpdatePath")

    End Sub

    Function GetData(ByVal selectCommand As String)

        Try

            Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
            Dim cmd As New System.Data.SqlClient.SqlCommand
            Dim reader As SqlClient.SqlDataReader = Nothing

            cmd.CommandText = selectCommand
            cmd.Connection = sqlConnection1

            Me.dataAdapter = New SqlClient.SqlDataAdapter(cmd)
            Dim commandBuilder As New SqlClient.SqlCommandBuilder(Me.dataAdapter)

            Dim table As New DataTable()
            table.Locale = System.Globalization.CultureInfo.InvariantCulture
            Me.dataAdapter.Fill(table)
            Me.bindingSource1.DataSource = Nothing
            Me.bindingSource1.DataSource = table



            ' Resize the DataGridView columns to fit the newly loaded content.
            Me.GridView1.BestFitColumns()

        Catch ex As OleDbException

        End Try
        GetData = ""

    End Function

    Function GetDataM3(ByVal selectCommand As String)

        Try

            Dim ConCM As OleDb.OleDbConnection
            ConCM = New OleDb.OleDbConnection(My.Settings.M3Connect)
            ConCM.Open()

            Dim cmd As New OleDbCommand(selectCommand, ConCM)

            Me.dataAdapterM3 = New OleDb.OleDbDataAdapter(cmd)
            Dim table As New DataTable
            table.Locale = System.Globalization.CultureInfo.InvariantCulture
            Me.dataAdapterM3.Fill(table)
            Me.bindingSource2.DataSource = table


            ' Resize the DataGridView columns to fit the newly loaded content.
            Me.GridView2.BestFitColumns()
        Catch ex As OleDbException

        End Try
        GetDataM3 = ""

    End Function

    Private Sub BarButtonItem1_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem1.ItemClick
        Me.dataAdapter.Update(CType(Me.bindingSource1.DataSource, DataTable))
    End Sub

    Public Sub BarButtonItem3_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem3.ItemClick
        GetData("SELECT * FROM F0010")
        GetDataM3("SELECT EAEMNM, EADIVI,EAEMNO,EAREAR,EAWHLO,EACAPH,EACAPH,EAPNCO,EAPHNO,EADEPT, EASRES FROM CEAEMP WHERE EADIVI = '" & Trim(My.Settings.DIVI) & "'")
    End Sub

    Private Sub SplitContainerControl1_SplitterPositionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles SplitContainerControl1.SplitterPositionChanged
        My.Settings.Split1 = Me.SplitContainerControl1.SplitterPosition
    End Sub

    Private Sub CopyOrUpdateToCadisToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CopyOrUpdateToCadisToolStripMenuItem.Click

        Dialog5.GroupControl1.Text = GridView2.GetRowCellValue(GridView2.FocusedRowHandle, "EAEMNM")

        If GridView2.GetRowCellValue(GridView2.FocusedRowHandle, "EAREAR") = "103387902" Then
            Dialog5.RadioButton2.Checked = True
        Else
            Dialog5.RadioButton1.Checked = True
        End If

        Dialog5.Show()

    End Sub

    Private Sub ChangeStatusToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChangeStatusToolStripMenuItem.Click

        Dim JLID As String

        Dim CADISCon As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim CADISCmd As New System.Data.SqlClient.SqlCommand
        Dim CADISReader As SqlClient.SqlDataReader = Nothing

        CADISCmd.Connection = CADISCon
        CADISCon.Open()

        With Me.GridView1

            JLID = .GetRowCellValue(.FocusedRowHandle, "JLID")

            CADISCmd.CommandText = "UPDATE dbo.F0010 SET  " & _
                                        "EASTAT = 'D' " & _
                                        "WHERE JLID = " & JLID
            CADISCmd.ExecuteNonQuery()
            CADISCon.Close()

            GetData("SELECT * FROM F0010")
        End With

    End Sub

    Private Sub EnableTechnicienToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EnableTechnicienToolStripMenuItem.Click

        Dim JLID As String

        Dim CADISCon As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim CADISCmd As New System.Data.SqlClient.SqlCommand
        Dim CADISReader As SqlClient.SqlDataReader = Nothing

        CADISCmd.Connection = CADISCon
        CADISCon.Open()

        With Me.GridView1

            JLID = .GetRowCellValue(.FocusedRowHandle, "JLID")

            CADISCmd.CommandText = "UPDATE dbo.F0010 SET  " & _
                                        "EASTAT = 'N' " & _
                                        "WHERE JLID = " & JLID
            CADISCmd.ExecuteNonQuery()
            CADISCon.Close()

            GetData("SELECT * FROM F0010")
        End With
    End Sub

    Private Sub ChangePasswordToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChangePasswordToolStripMenuItem.Click

        Dim JLID As String

        Dim CADISCon As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim CADISCmd As New System.Data.SqlClient.SqlCommand
        Dim CADISReader As SqlClient.SqlDataReader = Nothing

        CADISCmd.Connection = CADISCon
        CADISCon.Open()

        Dim RandomClass As New Random()
        Dim RandomNumber As Integer
        RandomNumber = RandomClass.Next(10000, 99999)

        With Me.GridView1
            JLID = .GetRowCellValue(.FocusedRowHandle, "JLID")

            CADISCmd.CommandText = "UPDATE dbo.F0010 SET  " & _
                                        "EAPASWD = '" & RandomNumber & "' " & _
                                        "WHERE JLID = " & JLID
            CADISCmd.ExecuteNonQuery()
            CADISCon.Close()

            GetData("SELECT * FROM F0010")
        End With

    End Sub



    Private Sub TranferToKratzerToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TranferToKratzerToolStripMenuItem.Click

        Dim s As String
        Dim filename As String = ""

        s = "TE;"
        With Me.GridView1


            s = s & Trim(.GetRowCellValue(.FocusedRowHandle, "EASTAT")) & ";"
            s = s & Trim(.GetRowCellValue(.FocusedRowHandle, "EAEMNO")) & ";"
            s = s & Trim(.GetRowCellValue(.FocusedRowHandle, "EACNTRY")) & ";"
            s = s & Trim(.GetRowCellValue(.FocusedRowHandle, "EACOMP")) & ";"
            s = s & Trim(.GetRowCellValue(.FocusedRowHandle, "EAREGN")) & ";"
            s = s & Trim(.GetRowCellValue(.FocusedRowHandle, "EAEMNM")) & ";"
            s = s & Trim(.GetRowCellValue(.FocusedRowHandle, "EAPASWD")) & ";"
            s = s & Trim(.GetRowCellValue(.FocusedRowHandle, "EAWHLO")) & ";"
            s = s & Trim(.GetRowCellValue(.FocusedRowHandle, "EATEL")) & ";"
            s = s & Trim(.GetRowCellValue(.FocusedRowHandle, "EAMOBL")) & ";"
            s = s & Trim(.GetRowCellValue(.FocusedRowHandle, "EAEMAIL")) & ";"
            s = s & Trim(.GetRowCellValue(.FocusedRowHandle, "EALANG"))
        End With


        'Write to export file  2011 03 21 07 00 00_CH_master-data.txt

        'Create file name
        filename = Format(Date.Today, "yyyyMMdd") & Format(TimeOfDay, "HHmmss") & "_" & Trim(GridView1.GetRowCellValue(GridView1.FocusedRowHandle, "EACNTRY")) & "_master-data.txt"

        Dim objReader As StreamWriter
        objReader = New StreamWriter(My.Settings.TechUpdatePath & "\" & filename, True, System.Text.Encoding.Default)
        objReader.Write(s)
        objReader.Close()

    End Sub

    Private Sub BarButtonItem5_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem5.ItemClick
        'Load data to excel
        Const filename As String = "c:\JLD004_Technicien_Lawson_Data.xls"

        If File.Exists(filename) Then
            File.Delete(filename)
        End If

        GridControl2.ExportToXls(filename)

        Dim app = New Microsoft.Office.Interop.Excel.Application
        app.Visible = True

        app.Workbooks.Open(System.IO.Path.GetFullPath(filename))
    End Sub

    Private Sub BarButtonItem6_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem6.ItemClick
        'Load data to excel
        Const filename As String = "c:\JLD004_Technicien_Kratzer_Data.xls"

        If File.Exists(filename) Then
            File.Delete(filename)
        End If

        GridControl1.ExportToXls(filename)

        Dim app = New Microsoft.Office.Interop.Excel.Application
        app.Visible = True

        app.Workbooks.Open(System.IO.Path.GetFullPath(filename))
    End Sub


    Private Sub DeleteTechnicienToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles DeleteTechnicienToolStripMenuItem.Click

        Dim JLID As String
        Dim x As String

        Dim CADISCon As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim CADISCmd As New System.Data.SqlClient.SqlCommand
        Dim CADISReader As SqlClient.SqlDataReader = Nothing

        x = XtraMessageBox.Show("Delete technicien ?", "Delete...", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation)

        If x = "1" Then
            CADISCmd.Connection = CADISCon
            CADISCon.Open()

            With Me.GridView1

                JLID = .GetRowCellValue(.FocusedRowHandle, "JLID")

                CADISCmd.CommandText = "DELETE F0010 " & _
                                            "WHERE JLID = " & JLID
                CADISCmd.ExecuteNonQuery()
                CADISCon.Close()

                GetData("SELECT * FROM F0010")
            End With
        End If

    End Sub

    Private Sub GridView1_RowCellStyle(sender As Object, e As DevExpress.XtraGrid.Views.Grid.RowCellStyleEventArgs) Handles GridView1.RowCellStyle
        Dim view As GridView = sender

        If e.Column.FieldName = "EASTAT" Then

            Dim myOtherColumnValue As String = view.GetRowCellValue(e.RowHandle, "EASTAT")

            If myOtherColumnValue = "N" Then e.Appearance.BackColor = Color.LightGreen
            If myOtherColumnValue = "D" Then e.Appearance.BackColor = Color.Red


        End If

    End Sub

End Class